Arbeitspaket (AP) 3: Management & Nutzung Räumliche Daten

Angaben Studierende(r) (fehlende Angaben ergänzen)

Vorname:	Mauricio
Nachname:	Saez
Immatrikulationsnummer:	12642112
Modul:	Data Science
Prüfungsdatum / Raum / Zeit:	07.10.2024 / Raum: SF O3.54 / 8:00 – 11:45
Erlaubte Hilfsmittel:	w.MA.XX.DS.24HS (Data Science) Open Book, Eigener Computer, Internet-Zugang
Nicht erlaubt:	Nicht erlaubt ist der Einsatz beliebiger Formen von generativer KI (z.B. Copilot, ChatGPT) sowie beliebige Formen von Kommunikation oder Kollaboration mit anderen Menschen.

Bewertungskriterien

(max. erreichbare Punkte: 48)

Kategorie	Beschreibung	Punkteverteilung
Code nicht lauffähig oder Ergebnisse nicht sinnvoll	Der Code enthält Fehler, die verhindern, dass er ausgeführt werden kann (z.B. Syntaxfehler) oder es werden Ergebnisse ausgegeben, welche nicht zur Fragestellung passen.	0 Punkte
Code lauffähig, aber mit gravierenden Mängeln	Der Code läuft, aber die Ergebnisse sind aufgrund wesentlicher Fehler unvollständig (z.B. fehlende Joins, gravierende Fehler in SQL-Abfragen). Nur geringer Fortschritt erkennbar.	25% der max. erreichbaren Punkte
Code lauffähig, aber mit mittleren Mängeln	Der Code läuft und liefert teilweise korrekte Ergebnisse, aber es gibt grössere Fehler (z.B. fehlende Spalten, unvollständige SQL-Abfragen). Die Ergebnisse sind nachvollziehbar, aber unvollständig oder ungenau.	50% der max. erreichbaren Punkte
Code lauffähig, aber mit minimalen Mängeln	Der Code läuft und liefert ein weitgehend korrektes Ergebnis, aber kleinere Fehler (z.B. falsche oder fehlende Sortierung, Rundung von Werten falsch) beeinträchtigen die Vollständigkeit des Ergebnisses.	75% der max. erreichbaren Punkte
Code lauffähig und korrekt	Der Code läuft einwandfrei und liefert das korrekte Ergebnis ohne Mängel.	100% der max. erreichbaren Punkte

Python Libraries und Settings

# Libraries
import os
import folium
import pandas as pd
import geopandas as gpd
from sqlalchemy import create_engine, text

# Ignore warnings
import warnings
warnings.filterwarnings("ignore")

print(os.getcwd())

/workspaces/python_postgresql_postgis

Vorbereitung (Hinweis: dieser Teil wird nicht bewertet)

1.) Starten Sie eine GitHub Codespaces Instanz auf Basis Ihres Forks des folgenden GitHub Repositories:

GitHub-Repository: https://github.com/mario-gellrich-zhaw/python_postgresql_postgis

WICHTIG!!! Verwenden Sie eine GitHub Codespaces Instanz mit ausreichend Arbeitsspeicher (4core, 16GB RAM).

Hinweis:

• Im Unterricht wurden bereits sämtliche Installationen und Einstellungen inkl. der Registrierung des Datenbank Servers auf pgAdmin vorgenommen.
• Falls Sie die Codespaces-Instanz neu erstellen müssen, folgen Sie bitte den detaillierten Erklärungen auf der README-Seite des GitHub Repositories.

2.) Erstellen und Testen Sie die Datenbankverbindung mit der 'osm_switzerland' Datenbank.

# Set up Database Connection
user = "pgadmin"
password = "geheim"
host = "localhost"
port = "5432"
database = "osm_switzerland"

# Erstellen der Connection URL
db_connection_url = "postgresql://" + user + ":" + password +\
                    "@" + host + ":" + port + "/" + database

# Erstellen SQLAlchemy Engine
engine = create_engine(db_connection_url)

# Test der Connection
with engine.connect() as connection:
    result = connection.execute(text('SELECT current_database()'))
    print(result.fetchone())

# Verbindung trennen
engine.dispose()

('osm_switzerland',)

Aufgaben (Dieser Teil wird bewertet!)

Hinweise zu den folgenden Aufgabenstellungen:

• In diesem Jupyter Notebook gibt es jeweils zwei Code-Zellen pro Aufgabe:
1. Eine Codezelle mit Python-Code und einem SQL-Statement für die Datenbank-Abfrage.
2. Eine Codezelle mit Python-Code für die Kartendarstellung der Ergebnisse der jeweiligen SQL-Abfrage.
• In den Codezellen für die Datenbank-Abfrage muss jeweils das SQL-Statement ergänzt werden.
• In den Codezellen für die Kartendarstellung muss nur dann der Python Code ergänzt werden, wenn in der Aufgabe danach gefragt wird.

Beachten Sie, dass für die Punktevergabe auch die weiteren Anforderungen zu den Fragen unter 'Details zur Aufgabenstellung' herangezogen werden.

Aufgabe (1): Erstellen Sie eine Abfrage sämtlicher Autoreparatur-Werkstätten in der Schweiz

Details zur Aufgabenstellung:

• Sie finden die benötigten Daten in der Tabelle 'planet_osm_point'.
• Stellen sie in der Ergebnistabelle die Spalten: osm_id, shop sowie die transformierte Geometrie als Spalte geom dar.
• Tipp: Die Geometry wird mit Hilfe der Funktion st_transform() transformiert, z.B.: st_transform(p.way, 4326) AS geom.
• Tipp: Autoreparatur-Werkstätten sind mit dem key:value Paar shop='car_repair' in der OpenStreetMap Map-Feature Übersicht angegeben.
• vgl: https://wiki.openstreetmap.org/wiki/Map_features

(max. erreichbare Punkte: 6)

# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)

# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
            h.osm_id,
            h.shop,
            h.name,
            ST_Transform(h.way, 4326) AS geom
        FROM planet_osm_point h
        WHERE h.shop = 'car_repair';"""

# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)

# Datenbankverbindung trennen
engine.dispose()

# Zeigen des GeoDataFrames
gdf

	osm_id	shop	name	geom
0	1811755810	car_repair	Grenzgarage	POINT (9.62898 47.45412)
1	9408250312	car_repair	Gebr. Wirth AG	POINT (9.63098 47.45327)
2	2539306181	car_repair	Lantech	POINT (9.58444 47.46663)
3	2530851973	car_repair	Garage Welpe	POINT (9.58777 47.46975)
4	3346119599	car_repair	Garage Martino GmbH	POINT (9.49096 47.47737)
...	...	...	...	...
1459	4386729493	car_repair	Garage Stahel	POINT (9.26639 47.55844)
1460	4366881989	car_repair	Autoviva VW und Seat	POINT (9.27967 47.54715)
1461	6092408245	car_repair	Nussberger Direktimport	POINT (9.34644 47.53412)
1462	1492440172	car_repair	Engeler Automobile	POINT (9.20147 47.63996)
1463	1493634725	car_repair	Morandi Group AG	POINT (9.19848 47.63967)

1464 rows × 4 columns

Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)

# Projektion definieren (WGS84)
if gdf.crs is None:
    gdf.set_crs(epsg=4326, inplace=True)
else:
    pass

# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()

# Initialisieren der Map
m = folium.Map(location=[lat, lon], 
               zoom_start=8, 
               tiles='CartoDB positron')

# Map settings
folium.GeoJson(
    gdf,
    name='map'
).add_to(m)

folium.LayerControl().add_to(m)

# Plot map
m

Make this Notebook Trusted to load map: File -> Trust Notebook

Aufgabe (2) Erstellen Sie eine Abfrage aller Biergärten in der Schweiz.

Details zur Aufgabenstellung:

• Sie finden die benötigten Daten in den Tabellen 'planet_osm_point'.
• Stellen Sie in der Ergebnistabelle die Spalten: osm_id, amenity, name und die transformierte Geometrie als Spalte geom dar.
• Tipp: Biergärten sind mit dem key:value Paar amenity='biergarten' in der OpenStreetMap Map-Feature Übersicht angegeben.
• vgl: https://wiki.openstreetmap.org/wiki/Map_features

(max. erreichbare Punkte: 6)

# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)  

# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
            h.osm_id,
            h.amenity,
            h.name,
            ST_Transform(h.way, 4326) AS geom
        FROM planet_osm_point h
        WHERE h.amenity = 'biergarten';"""

# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)

# Datenbankverbindung trennen
engine.dispose()

# Zeigen des GeoDataFrames
gdf.head()

	osm_id	amenity	name	geom
0	704467869	biergarten	Bierhalle	POINT (9.6068 47.40694)
1	423833242	biergarten	Brasserie-Bar de la Poste	POINT (6.93199 46.99151)
2	746772927	biergarten	Buvette de l'alpage du col du Lein	POINT (7.15997 46.11045)
3	370416485	biergarten	Bar des Etablons	POINT (7.23621 46.14038)
4	6109596184	biergarten	Dolce Vita Gelati Bar	POINT (7.6897 46.68853)

Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)

# Projektion definieren (WGS84)
if gdf.crs is None:
    gdf.set_crs(epsg=4326, inplace=True)
else:
    pass

# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()

# Initialisieren der Map
m = folium.Map(location=[lat, lon], 
               zoom_start=8, 
               tiles='CartoDB positron')

# Map settings
folium.GeoJson(
    gdf,
    name='map'
).add_to(m)

folium.LayerControl().add_to(m)

# Plot map
m

Make this Notebook Trusted to load map: File -> Trust Notebook

Aufgabe (3): Erstellen Sie eine Abfrage aller Gebäude in der Stadthausstrasse in Winterthur, welche vollständige Adressangaben besitzen.

Details zur Aufgabenstellung:

• Sie finden die benötigten Daten in der Tabelle 'planet_osm_polygon'.
• Vollständige Adressangabe bedeutet: Strassenname, Haunummer, PLZ, Gemeindename sind vorhanden.
• Stellen Sie in der Ergebnistabelle sämtliche Adressangaben sowie die transformierte Geometrie als Spalte geom dar.
• Verwenden Sie für die Darstellung als Hintergrundkarte ein Satellitenbild (ESRIWorldImagery) als maptile.
• Tipp: Gebäude sind in der Spalte 'building' klassifiziert. Mit WHERE building IS NOT NULL können Sie Gebäude filtern.

(max. erreichbare Punkte: 6)

# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)  

# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
                p.osm_id,
                p."addr:street",
                p."addr:housenumber",
                p."addr:city",
                p."addr:postcode",
                p.building,
                st_transform(p.way, 4326) AS geom
        FROM
                public.planet_osm_polygon AS p
        WHERE
                p."addr:street" IS NOT NULL
                AND p."addr:housenumber" IS NOT NULL
                AND p."building" IS NOT NULL
                AND p."addr:city"='Winterthur'
                AND p."addr:street"='Stadthausstrasse'
                
                """

# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)

# Datenbankverbindung trennen
engine.dispose()

# Zeigen des GeoDataFrames
gdf

	osm_id	addr:street	addr:housenumber	addr:city	addr:postcode	building	geom
0	75027485	Stadthausstrasse	31	Winterthur	8400	office	POLYGON ((8.73162 47.50041, 8.73165 47.50029, ...
1	75027472	Stadthausstrasse	35	Winterthur	8400	apartments	POLYGON ((8.73153 47.5004, 8.73157 47.50026, 8...
2	75027503	Stadthausstrasse	37	Winterthur	8400	apartments	POLYGON ((8.73146 47.5004, 8.73149 47.50028, 8...
3	75027432	Stadthausstrasse	39	Winterthur	8400	office	POLYGON ((8.73111 47.50039, 8.73115 47.50025, ...
4	230520600	Stadthausstrasse	61	Winterthur	8400	office	POLYGON ((8.72952 47.5002, 8.72955 47.50006, 8...
...	...	...	...	...	...	...	...
56	24804763	Stadthausstrasse	22	Winterthur	8400	office	POLYGON ((8.72448 47.49982, 8.72453 47.4997, 8...
57	26992511	Stadthausstrasse	24	Winterthur	8400	retail	POLYGON ((8.72405 47.49972, 8.72407 47.49967, ...
58	134980581	Stadthausstrasse	10b	Winterthur	8400	yes	POLYGON ((8.72652 47.50075, 8.72661 47.50063, ...
59	9264543	Stadthausstrasse	4a	Winterthur	8400	government	POLYGON ((8.73111 47.50115, 8.73112 47.50085, ...
60	22301937	Stadthausstrasse	4	Winterthur	8400	apartments	POLYGON ((8.73232 47.50109, 8.73233 47.50107, ...

61 rows × 7 columns

Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)

# Projektion definieren (WGS84)
if gdf.crs is None:
    gdf.set_crs(epsg=4326, inplace=True)
else:
    pass

# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()

# Initialisieren der Map
m = folium.Map(location=[lat, lon], 
               zoom_start=17, 
               tiles='ESRIWorldImagery')

# Map settings
folium.Choropleth(
    geo_data=gdf,
    name='map',
    fill_color='greenyellow'
).add_to(m)

folium.LayerControl().add_to(m)

# Plot map
m

Make this Notebook Trusted to load map: File -> Trust Notebook

Aufgabe (4): Erstellen Sie eine Abfrage aller Strassen in der Schweiz, welche als 'motorway' klassifiziert sind.

Details zur Aufgabenstellung:

• Sie finden die benötigten Informationen in der Tabelle 'planet_osm_roads'.
• Stellen Sie in der Ergebnistabelle die Spalten: osm_id, highway und die transformierte Geometrie als Spalte geom dar.
• Tipp: Motorways sind mit dem key:value Paar highway='motorway' in der OpenStreetMap Map-Feature Übersicht angegeben.
• vgl: https://wiki.openstreetmap.org/wiki/Map_features

(max. erreichbare Punkte: 6)

# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)  

# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen

sql =  """-- Create buffer around major roads
        SELECT 
            1 as group_id,
            ST_TRANSFORM(p.way::geometry, 4326) AS geom
        FROM public.planet_osm_roads AS p
        WHERE
            highway = 'motorway';"""

# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)

# Datenbankverbindung trennen
engine.dispose()

# Zeigen des GeoDataFrames
gdf

	group_id	geom
0	1	LINESTRING (9.64218 47.43433, 9.6422 47.43402,...
1	1	LINESTRING (9.64232 47.43412, 9.6423 47.43469)
2	1	LINESTRING (9.6423 47.43469, 9.64232 47.43504,...
3	1	LINESTRING (9.64312 47.43793, 9.6429 47.43753,...
4	1	LINESTRING (9.64235 47.43533, 9.6424 47.43568,...
...	...	...
8263	1	LINESTRING (9.15834 47.65468, 9.15789 47.65424...
8264	1	LINESTRING (9.15371 47.65075, 9.15343 47.6503,...
8265	1	LINESTRING (9.14594 47.63688, 9.14599 47.63692...
8266	1	LINESTRING (9.14357 47.63107, 9.14364 47.63151)
8267	1	LINESTRING (9.14365 47.63224, 9.14352 47.63152)

8268 rows × 2 columns

Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)

# Projektion definieren (WGS84)
if gdf.crs is None:
    gdf.set_crs(epsg=4326, inplace=True)
else:
    pass

# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()

# Initialisieren der Map
m = folium.Map(location=[lat, lon], 
               zoom_start=9, 
               tiles='CartoDB positron')

# Map settings
folium.Choropleth(
    geo_data=gdf,
    name='map',
    line_weight=3,
    line_color='red'
).add_to(m)

folium.LayerControl().add_to(m)

# Plot map
m

Make this Notebook Trusted to load map: File -> Trust Notebook

Aufgabe (5): Erstellen Sie eine Abfrage aller Schweizer Flüsse. Generieren Sie zusätzlich Buffer um die Flüsse mit einer Breite von 2000m.

Details zur Aufgabenstellung:

• Sie finden die benötigten Informationen in der Tabelle 'planet_osm_line'.
• Stellen Sie in der Ergebnistabelle die Spalten: osm_id, waterway sowie die transformierte Geometrie als Spalte geom dar.
• Tipp: Flüsse sind mit dem key:value Paar waterway='river' in der OpenStreetMap Map-Feature Übersicht angegeben.
• vgl: https://wiki.openstreetmap.org/wiki/Map_features
• Tipp: Per Default wird für jedes Fluss-Segment ein separater Buffer erstellt. Es ist nicht notwendig daraus einen einzelnen Buffer zu generieren.

(max. erreichbare Punkte: 8)

# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)  

# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """-- Create buffer around major rivers
        SELECT 
            1 as group_id,
        ST_TRANSFORM(ST_UNION(ST_Buffer(p.way::geometry, 2000)), 4326) AS geom
        FROM public.planet_osm_line AS p
        WHERE
            waterway = 'river'
            
            ;"""

# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)

# Datenbankverbindung trennen
engine.dispose()

# Zeigen des GeoDataFrames
gdf

	group_id	geom
0	1	MULTIPOLYGON (((5.92959 46.11614, 5.92771 46.1...

Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)

# Projektion definieren (WGS84)
if gdf.crs is None:
    gdf.set_crs(epsg=4326, inplace=True)
else:
    pass

# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()

# Initialisieren der Map
m = folium.Map(location=[lat, lon], 
               zoom_start=8, 
               tiles='CartoDB positron')

# Map settings
folium.Choropleth(
    geo_data=gdf,
    name='map',
    fill_color='greenyellow'
).add_to(m)

folium.LayerControl().add_to(m)

# Plot map
m

Make this Notebook Trusted to load map: File -> Trust Notebook

Aufgabe (6): Erstellen Sie eine Abfrage der Bäckerei-Geschäfte in Zürich und Winterthur.

Details zur Aufgabenstellung:

• Sie finden die benötigten Daten in den Tabellen 'planet_osm_point' (Backereien).
• Verwenden Sie die Städtenamen aus den Adressangaben für die Abfrage der Bäckerei-Standorte (Zürich, Winterthur).
• Stellen Sie in der Ergebnistabelle die Spalten: osm_id, shop, name, "addr:city" sowie die transformierte Geometrie als geom dar.
• Wählen Sie eine Satelliten Karte von ESRI als Hintergrundkarte (maptile).
• Sortieren Sie die Bäckerei-Geschäfte aufsteigend nach osm_id.
• Tipp: Bäckerei-Geschäfte sind mit dem key:value Paar shop='bakery' in der OpenStreetMap Map-Feature Übersicht angegeben.
• vgl: https://wiki.openstreetmap.org/wiki/Map_features

(max. erreichbare Punkte: 8)

# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)  

# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
            h.osm_id,
            h.shop,
            h.name,
            h."addr:city",
            ST_Transform(h.way, 4326) AS geom
        FROM planet_osm_point h
        WHERE h.shop = 'bakery'
        AND h."addr:city" IN ('Winterthur','Zürich')
        ORDER BY h.osm_id ASC

        ;"""

# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)

# Datenbankverbindung trennen
engine.dispose()

# Zeigen des GeoDataFrames
gdf

	osm_id	shop	name	addr:city	geom
0	252457457	bakery	Steiner Flughafebeck	Zürich	POINT (8.49779 47.40311)
1	266630770	bakery	Brezelkönig	Zürich	POINT (8.48875 47.39147)
2	267879346	bakery	Walter Buchmann	Zürich	POINT (8.51892 47.36239)
3	268602152	bakery	Moser's	Zürich	POINT (8.54945 47.3632)
4	270794699	bakery	John Baker	Zürich	POINT (8.56645 47.36493)
...	...	...	...	...	...
101	10082330824	bakery	RAM3	Winterthur	POINT (8.74193 47.49395)
102	10884168277	bakery	Juliette	Zürich	POINT (8.5328 47.36658)
103	10946043353	bakery	Babu's Bakery	Zürich	POINT (8.51568 47.37473)
104	10955852823	bakery	Täglich Brot	Zürich	POINT (8.50705 47.36079)
105	11951988149	bakery	Wagner	Zürich	POINT (8.51763 47.3698)

106 rows × 5 columns

Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)

# Projektion definieren (WGS84)
if gdf.crs is None:
    gdf.set_crs(epsg=4326, inplace=True)
else:
    pass

# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()

# Initialisieren der Map
m = folium.Map(location=[lat, lon], 
               zoom_start=11, 
               tiles='EsriWorldImagery')

# Map settings
folium.GeoJson(
    gdf,
    name='map',
).add_to(m)

folium.LayerControl().add_to(m)

# Plot map
m

Make this Notebook Trusted to load map: File -> Trust Notebook

Aufgabe (7): Erstellen Sie eine Abfrage sämtlicher Coiffeur-Geschäfte in einem Radius von 500m um den Hauptbahnhof in Zürich.

Details zur Aufgabenstellung:

• Sie finden die Daten in der Tabelle 'planet_osm_point'.
• Berechnen Sie in der Abfrage die Distanz jedes Coiffeur-Geschäfts zum Hauptbahnhof in Metern als Spalte 'distance_meters'.
• Stellen Sie in der Ergebnistabelle die Spalten: osm_id, shop, name, distance_meters, sowie die transformierte Geometrie als geom dar.
• Wählen Sie eine Satelliten Karte von ESRI als Hintergrundkarte (maptile).
• Integrieren Sie in die Kartendarstellung den Namen (Spalte 'name') der Coiffeur-Geschäfte als Popup.
• Tipp: Coiffeur-Geschäfte sind mit dem key:value Paar shop='hairdresser' in der OpenStreetMap Map-Feature Übersicht angegeben.
• vgl: https://wiki.openstreetmap.org/wiki/Map_features

(max. erreichbare Punkte: 8)

# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)  

# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
            p.osm_id,
            p.shop,
            p.name,
            ST_Distance(
                ST_Transform(p.way, 4326)::geography,
                -- my coordinates
                ST_SetSRID(ST_MakePoint(8.53936, 47.3781), 4326)::geography
            ) AS distance_meters,
            ST_TRANSFORM(p.way, 4326) AS geom
        FROM
            planet_osm_point AS p
        WHERE
            p.shop = 'hairdresser'
            AND ST_DWithin(
                ST_Transform(p.way, 4326)::geography,
                -- my coordinates
                ST_SetSRID(ST_MakePoint(8.53936, 47.3781), 4326)::geography,
                500
            )
        ORDER BY distance_meters;"""

# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)

# Datenbankverbindung trennen
engine.dispose()

# Zeigen des GeoDataFrames
gdf.head()

	osm_id	shop	name	distance_meters	geom
0	4833061523	hairdresser	André Joe Coiffure	72.419054	POINT (8.54016 47.37774)
1	4424939218	hairdresser	McCoiffure	111.635316	POINT (8.53791 47.37791)
2	4244059289	hairdresser	Art Coiffure Kaiser	133.439602	POINT (8.53788 47.37745)
3	4424939145	hairdresser	Saleh	151.462092	POINT (8.54071 47.37709)
4	693318659	hairdresser	Coiffeur Insieme	168.219919	POINT (8.53992 47.37664)

Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)

# Projektion definieren (WGS84)
if gdf.crs is None:
    gdf.set_crs(epsg=4326, inplace=True)
else:
    pass

# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()

# Initialisieren der Map
m = folium.Map(location=[lat, lon], 
               zoom_start=16, 
               tiles='EsriWorldImagery')

# Map settings
folium.GeoJson(
    gdf,
    name='map',
    popup=folium.GeoJsonPopup(fields=['name'])
).add_to(m)

folium.LayerControl().add_to(m)

# Plot map
m

Make this Notebook Trusted to load map: File -> Trust Notebook

Jupyter notebook --footer info-- (please always provide this at the end of each notebook)

import os
import platform
import socket
from platform import python_version
from datetime import datetime

print('-----------------------------------')
print(os.name.upper())
print(platform.system(), '|', platform.release())
print('Datetime:', datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
print('Python Version:', python_version())
print('IP Address:', socket.gethostbyname(socket.gethostname()))
print('-----------------------------------')

-----------------------------------
POSIX
Linux | 6.5.0-1025-azure
Datetime: 2024-10-07 08:55:46
Python Version: 3.12.1
IP Address: 127.0.0.1
-----------------------------------